1% completed
Problem
Table: Student
+-------------+---------+
| Column Name | Type |
+-------------+---------+
| name | varchar |
| continent | varchar |
+-------------+---------+
This table may contain duplicate rows.
Each row of this table indicates the name of a student and the continent they came from.
Problem Definition
A school has students from Asia, Europe, and America.
Write a solution to pivot the continent column in the Student table so that each name is sorted alphabetically and displayed underneath its corresponding continent. The output headers should be America, Asia, and Europe, respectively.
Example
Output
Try It Yourself
Student =
Solution
To pivot the Student table such that each student's name is displayed under their respective continent (America, Asia, Europe) and sorted alphabetically, we can follow a systematic approach. This involves assigning row numbers to each student within their continent based on alphabetical order and then aggregating the names accordingly.
Approach Overview
-
Assign Row Numbers to Students Within Each Continent:
- For each continent, sort the students' names alphabetically and assign a sequential row number. This helps in aligning students from different continents side by side in the pivoted result.
-
Pivot the Data Based on Row Numbers:
- Transform the sorted list into a pivoted format where each row corresponds to a specific row number, and each column represents a continent. This aligns students from different continents based on their row numbers.
-
Select and Order the Final Results:
- Retrieve the pivoted data, ensuring that the continents are displayed as separate columns (
America,Asia,Europe). Sort the results based on the row numbers to maintain the alphabetical order.
- Retrieve the pivoted data, ensuring that the continents are displayed as separate columns (
SQL Query
SELECT Max(CASE WHEN continent = 'America' THEN NAME END) AS America, Max(CASE WHEN continent = 'Asia' THEN NAME END) AS Asia, Max(CASE WHEN continent = 'Europe' THEN NAME END) AS Europe FROM (SELECT continent, NAME, Row_number() OVER ( partition BY continent ORDER BY NAME) AS rn FROM Student)i GROUP BY rn
Step-by-Step Approach
Step 1: Assign Row Numbers to Students Within Each Continent
For each continent (America, Asia, Europe), sort the students' names alphabetically and assign a sequential row number. This row number (rn) will be used to align students from different continents side by side in the pivoted result.
SQL Query:
SELECT continent, NAME, ROW_NUMBER() OVER ( PARTITION BY continent ORDER BY NAME ) AS rn FROM Student;
Explanation:
ROW_NUMBER() OVER (PARTITION BY continent ORDER BY NAME) AS rn:- Assigns a unique sequential number to each student within their continent based on alphabetical order of their names.
PARTITION BY continent:- Divides the data into partitions for each continent, ensuring row numbers restart for each continent.
Intermediate Output After Step 1:
+-----------+----------+----+ | continent | NAME | rn | +-----------+----------+----+ | America | Jack | 1 | | America | Jane | 2 | | Asia | Xi | 1 | | Europe | Pascal | 1 | +-----------+----------+----+
Step 2: Pivot the Data Based on Row Numbers
Transform the sorted list into a pivoted format where each row corresponds to a specific row number (rn), and each column represents a continent. This aligns students from different continents side by side based on their row numbers.
SQL Query:
SELECT Max(CASE WHEN continent = 'America' THEN NAME END) AS America, Max(CASE WHEN continent = 'Asia' THEN NAME END) AS Asia, Max(CASE WHEN continent = 'Europe' THEN NAME END) AS Europe FROM ( SELECT continent, NAME, ROW_NUMBER() OVER ( PARTITION BY continent ORDER BY NAME ) AS rn FROM Student ) i GROUP BY rn;
Explanation:
CASE WHEN continent = 'America' THEN NAME END AS America:- Extracts the student's name if they belong to
America.
- Extracts the student's name if they belong to
MAX(...) AS America:- Aggregates the names based on the row number (
rn). Since there's only one name per continent perrn,MAXeffectively selects that name.
- Aggregates the names based on the row number (
GROUP BY rn:- Groups the data by the row number to align students from different continents in the same row.
Intermediate Output After Step 2:
+---------+------+---------+ | America | Asia | Europe | +---------+------+---------+ | Jack | Xi | Pascal | | Jane | NULL | NULL | +---------+------+---------+
Step 3: Select and Order the Final Results
Retrieve the pivoted data with columns America, Asia, and Europe. Each row represents students from different continents aligned based on their alphabetical order. The result should be ordered by rn to maintain the sequence.
Final SQL Query:
SELECT Max(CASE WHEN continent = 'America' THEN NAME END) AS America, Max(CASE WHEN continent = 'Asia' THEN NAME END) AS Asia, Max(CASE WHEN continent = 'Europe' THEN NAME END) AS Europe FROM ( SELECT continent, NAME, ROW_NUMBER() OVER ( PARTITION BY continent ORDER BY NAME ) AS rn FROM Student ) i GROUP BY rn ORDER BY rn ASC;
Explanation:
ORDER BY rn ASC:- Ensures that the rows are ordered based on the row numbers, maintaining the alphabetical sequence of student names within each continent.
Final Output:
+---------+------+---------+ | America | Asia | Europe | +---------+------+---------+ | Jack | Xi | Pascal | | Jane | NULL | NULL | +---------+------+---------+
On this page
Problem
Problem Definition
Example
Output
Try It Yourself
Solution
Approach Overview
SQL Query
Step-by-Step Approach